﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BOL;
using DAL;
using System.Data.Linq;
using System.Data;
using System.Windows.Forms;
using System.Security.Cryptography;
using System.IO;


namespace BLL
{
    public class HamChung
    {
        //mã hóa md5 cho cái mật khẩu
        public string EncodeMD5(string pass)
        {
            MD5CryptoServiceProvider md5 = new MD5CryptoServiceProvider();
            byte[] bs = System.Text.Encoding.UTF8.GetBytes(pass);
            bs = md5.ComputeHash(bs);
            System.Text.StringBuilder s = new System.Text.StringBuilder();
            foreach (byte b in bs)
            {
                s.Append(b.ToString("x2").ToLower());
            }
            pass = s.ToString();
            return pass;
        }

    }

    public class FormDangNhap
    {
        QLThueXeDataContext db = new QLThueXeDataContext();
        HamChung hc = new HamChung();
        
        public int Checklogin(string tendn, string matkhau)
        {
            try
            {
                int kq = 0;
                if (db.NHANVIENs.Where(nv => nv.TEN_DANGNHAP.Trim() == tendn && nv.KHOA == false).FirstOrDefault() != null)
                {
                    if ((db.NHANVIENs.Where(nv => nv.TEN_DANGNHAP.Trim() == tendn).FirstOrDefault().MAT_KHAU).Trim() == hc.EncodeMD5(matkhau))
                        kq = 1;
                    else kq = 2;
                }
                return kq;
            }
            catch { return 3; }
        }

        public List<ThongTinNhanVien> LayDSNhanVien(string taikhoan)
        {
            var bien = (from nv in db.NHANVIENs
                        where nv.TEN_DANGNHAP == taikhoan
                        select new ThongTinNhanVien
                        {
                            manv = nv.MA_NV,
                            hotennv = nv.HOTEN_NV,
                            ngaysinhnv = nv.NGAYSINH_NV.Value,
                            gioitinhnv = nv.GIOITINH,
                            sdtnv = nv.SDT_NV,
                            diachinv = nv.DIACHI_NV,
                            tendangnhap = nv.TEN_DANGNHAP,
                            matkhau = nv.MAT_KHAU,
                            chucvu = nv.CHUCVU,
                            hinhanh = new System.IO.MemoryStream(nv.HINHANH.ToArray()),
                            khoa = nv.KHOA.Value
                        }).ToList<ThongTinNhanVien>();
            return bien;
        }
    }

    public class FormMain
    {
        QLThueXeDataContext db = new QLThueXeDataContext();
        
       public List<ThongTinThueXe> LayTTThueXe(DateTime dt)
        {
            var Bien = (from tx in db.ThueXes
                        from kh in db.KhachHangs
                        from x in db.Xes
                        from lx in db.LoaiXes                        
                        where  tx.ID_KhachHang == kh.ID_KhachHang && x.LoaiXe == lx.LoaiXe1 && tx.Ma_Xe == x.Ma_Xe && tx.NgayGio_Thue.Value.Date == dt.Date && tx.Huy == false
                        select new ThongTinThueXe
                        {
                            matx = tx.Ma_ThueXe,
                            ngaygiothue = DateTime.Parse(tx.NgayGio_Thue.ToString()),
                            hotenkh = kh.HoTen_KH,
                            sdtkh = kh.SDT_KH,
                            diachikh = kh.DiaChi_KH,
                            hangxe = lx.LoaiXe1,
                            maxe = x.Ma_Xe,
                            tenxe = x.TenXe,
                            sogiothue = (int)tx.SoGio_Thue,
                            sophutthue = (int)tx.SoPhut_Thue,
                            nv_lap = tx.NhanVien_Lap,
                            nv_huy = tx.NhanVien_Huy,
                            huy = tx.Huy.Value,
                            thanhtoan = tx.NoTien.Value
                        }).ToList<ThongTinThueXe>();
            return Bien;
        }

        public List<ThongTinXe> LayTTXe()
        {
            var Bien = (from xe in db.Xes
                        select new ThongTinXe
                        {
                            max = xe.Ma_Xe,
                            tenx = xe.TenXe,
                            bienso = xe.BienSo,
                            hangx = xe.LoaiXe
                        }).ToList<ThongTinXe>();
            return Bien;
        }

        public List<ThongTinTooltip> LayttDatXeTooltip(string mathuexe)
        {
            var bien = (from tx in db.ThueXes
                        from kh in db.KhachHangs
                        where tx.ID_KhachHang == kh.ID_KhachHang && tx.Ma_ThueXe == mathuexe
                        select new ThongTinTooltip
                        {
                            tenkh = kh.HoTen_KH,
                            sdtkh = kh.SDT_KH,
                            diachiKH = kh.DiaChi_KH,
                            ngaygioBD = tx.NgayGio_Thue.Value,
                            sogiothue = tx.SoGio_Thue.Value,
                            sophutthue = tx.SoPhut_Thue.Value,
                            thanhtoan = tx.NoTien.Value

                        }
                      ).ToList<ThongTinTooltip>();
            return bien;
        }
    }

    public class FormDatVe
    {
        QLThueXeDataContext db = new QLThueXeDataContext();

        public string madatvelonnhat()
        {
            string ma = (from m in db.ThueXes
                         orderby m.Ma_ThueXe descending
                         select m.Ma_ThueXe).FirstOrDefault();
            return ma;
        }

        public int laygia(string hangxe, DateTime dt)
        {
            int gia = (from g in db.GiaThues
                       where g.LoaiXe == hangxe && g.NgayApDung == (from t in db.GiaThues where t.LoaiXe == hangxe && t.NgayApDung <= dt  orderby t.NgayApDung descending select t.NgayApDung).FirstOrDefault()
                       select g.GiaThue1.Value
                       ).FirstOrDefault();
            return gia;
        }

        public string layhangxe(string maxe)
        {
            string hangx = (from hx in db.Xes
                            where hx.Ma_Xe == maxe
                            select hx.LoaiXe
                                ).FirstOrDefault();
            return hangx;
        }

        public int madatKHlonnhat()
        {
            int ma = (from kh in db.KhachHangs
                         orderby kh.ID_KhachHang descending
                         select kh.ID_KhachHang).FirstOrDefault();
            return ma;
        }

        public void ThemDatXe(string matx, string maxe, DateTime ngaygiothue, int sogiothue, int sophutthue, bool huy, bool thanhtoan, string manv, string hotenkh, string sdt, string diachi)
        {
            KhachHang kh = new KhachHang();
            kh.HoTen_KH = hotenkh;
            kh.SDT_KH = sdt;
            kh.DiaChi_KH = diachi;
            db.KhachHangs.InsertOnSubmit(kh);
            db.SubmitChanges();

            ThueXe tx = new ThueXe();
            tx.Ma_ThueXe = matx;
            tx.Ma_Xe = maxe;
            tx.ID_KhachHang = madatKHlonnhat();
            tx.NgayGio_Thue = ngaygiothue;
            tx.SoGio_Thue = sogiothue;
            tx.SoPhut_Thue = sophutthue;
            tx.Huy = huy;
            tx.NoTien = thanhtoan;
            tx.NhanVien_Lap = manv;
            db.ThueXes.InsertOnSubmit(tx);
            db.SubmitChanges();
        }

        public void HuyDatXe(string madatxe, bool huy, string lydohuy, string manv)
        {
            ThueXe tx = db.ThueXes.Where(t => t.Ma_ThueXe == madatxe).FirstOrDefault();
            tx.Huy = huy;
            tx.LyDoHuy = lydohuy;
            tx.NhanVien_Huy = manv;
            db.SubmitChanges();
        }

        public void ThanhToan(string madatxe, bool thanhtoan)
        {
            ThueXe tx = db.ThueXes.Where(t => t.Ma_ThueXe == madatxe).FirstOrDefault();
            tx.NoTien = thanhtoan;
            db.SubmitChanges();
        }
    }

    public class FormNhanVien
    {
        QLThueXeDataContext db = new QLThueXeDataContext();

        public List<ThongTinNhanVien> LayDSNhanVien()
        {
            var bien = (from nv in db.NHANVIENs
                        select new ThongTinNhanVien
                        {
                            manv = nv.MA_NV,
                            hotennv = nv.HOTEN_NV,
                            ngaysinhnv = nv.NGAYSINH_NV.Value,
                            gioitinhnv = nv.GIOITINH,
                            sdtnv = nv.SDT_NV,
                            diachinv = nv.DIACHI_NV,
                            tendangnhap = nv.TEN_DANGNHAP,
                            matkhau = nv.MAT_KHAU,
                            chucvu = nv.CHUCVU,
                            hinhanh = new System.IO.MemoryStream(nv.HINHANH.ToArray()),
                            khoa = nv.KHOA.Value
                        }).ToList<ThongTinNhanVien>();
            return bien;
        }

        public List<ThongTinNhanVien> LayDSNhanVien1(string manv)
        {
            var bien = (from nv in db.NHANVIENs
                        where nv.MA_NV == manv
                        select new ThongTinNhanVien
                        {
                            manv = nv.MA_NV,
                            hotennv = nv.HOTEN_NV,
                            ngaysinhnv = nv.NGAYSINH_NV.Value,
                            gioitinhnv = nv.GIOITINH,
                            sdtnv = nv.SDT_NV,
                            diachinv = nv.DIACHI_NV,
                            tendangnhap = nv.TEN_DANGNHAP,
                            matkhau = nv.MAT_KHAU,
                            chucvu = nv.CHUCVU,
                            hinhanh = new System.IO.MemoryStream(nv.HINHANH.ToArray()),
                            khoa = nv.KHOA.Value
                        }).ToList<ThongTinNhanVien>();
            return bien;
        }

        public MemoryStream layhinhnv(string manv)
        {
            return new MemoryStream(db.NHANVIENs.Where(nv => nv.MA_NV == manv).FirstOrDefault().HINHANH.ToArray());
        }

        public void themnv(string ma, string ten, DateTime ngaysinh, string gioitinh,string sdt, string diachi, string tendn, string matkhau, string chucvu, bool khoa, MemoryStream hinhanh)
        {
            NHANVIEN nv = new NHANVIEN();
            nv.MA_NV = ma;
            nv.HOTEN_NV = ten;
            nv.NGAYSINH_NV = ngaysinh;
            nv.GIOITINH = gioitinh;
            nv.SDT_NV = sdt;
            nv.DIACHI_NV = diachi;
            nv.TEN_DANGNHAP = tendn;
            nv.MAT_KHAU = matkhau;
            nv.CHUCVU = chucvu;
            nv.KHOA = khoa;
            nv.HINHANH = hinhanh.ToArray();

            db.NHANVIENs.InsertOnSubmit(nv);
            db.SubmitChanges();
        }

        public void suanv(string ma, string ten, DateTime ngaysinh, string gioitinh,string sdt, string diachi,string chucvu, bool khoa, MemoryStream hinhanh)
        {
            NHANVIEN nv = db.NHANVIENs.Where(v => v.MA_NV == ma).FirstOrDefault();
            nv.HOTEN_NV = ten;
            nv.NGAYSINH_NV = ngaysinh;
            nv.GIOITINH = gioitinh;
            nv.SDT_NV = sdt;
            nv.DIACHI_NV = diachi;
            nv.CHUCVU = chucvu;
            nv.KHOA = khoa;
            nv.HINHANH = hinhanh.ToArray();

            db.SubmitChanges();
        }

        public void suanv2(string ma, string ten, DateTime ngaysinh, string gioitinh, string sdt, string diachi, MemoryStream hinhanh)
        {
            NHANVIEN nv = db.NHANVIENs.Where(v => v.MA_NV == ma).FirstOrDefault();
            nv.HOTEN_NV = ten;
            nv.NGAYSINH_NV = ngaysinh;
            nv.GIOITINH = gioitinh;
            nv.SDT_NV = sdt;
            nv.DIACHI_NV = diachi;            
            nv.HINHANH = hinhanh.ToArray();

            db.SubmitChanges();
        }

        public string madatNVlonnhat()
        {
            string ma = (from m in db.NHANVIENs
                         orderby m.MA_NV descending
                         select m.MA_NV).FirstOrDefault();
            return ma;
        }

        public bool checktendn(string tendn)
        {
            if (db.NHANVIENs.Where(nv => nv.TEN_DANGNHAP == tendn).FirstOrDefault() != null)
                return false;
            else return true;
        }
        public void doimk_Ql(string manv, string matkhaumoi)
        {
            NHANVIEN nv = db.NHANVIENs.Where(v => v.MA_NV == manv).FirstOrDefault();
            nv.MAT_KHAU = matkhaumoi;
            db.SubmitChanges();
        }
    }

    public class FormHangXe
    {
        QLThueXeDataContext db = new QLThueXeDataContext();
        public bool checkhangxe(string hangxe)
        {
           if(db.LoaiXes.Where(hx => hx.LoaiXe1 == hangxe).FirstOrDefault() != null)
               return false;
           else return true;
        }

        public List<ThongTinHangXe> LayDSHangXe()
        {
            var bien = (from hx in db.LoaiXes
                        select new ThongTinHangXe
                        {
                            hangxe = hx.LoaiXe1,
                            ghichu = hx.GhiChu_LX
                        }).ToList<ThongTinHangXe>();
            return bien;
        }

        public void themHx(string hangxe, string ghichu)
        {
            LoaiXe lx = new LoaiXe();
            lx.LoaiXe1 = hangxe;
            lx.GhiChu_LX = ghichu;

            db.LoaiXes.InsertOnSubmit(lx);
            db.SubmitChanges();
        }

        public void suaHx(string hangxe, string ghichu)
        {
            LoaiXe lx = db.LoaiXes.Where(v => v.LoaiXe1 == hangxe).FirstOrDefault();
            lx.GhiChu_LX = ghichu;
            
            db.SubmitChanges();
        }

        public void XoaHx(string hangxe)
        {
            LoaiXe lx = db.LoaiXes.Where(v => v.LoaiXe1 == hangxe).FirstOrDefault();
            db.LoaiXes.DeleteOnSubmit(lx);
            db.SubmitChanges();
        }
    }

    public class FormXe
    {
        QLThueXeDataContext db = new QLThueXeDataContext();
        public string madatXElonnhat()
        {
            string ma = (from m in db.Xes
                         orderby m.Ma_Xe descending
                         select m.Ma_Xe).FirstOrDefault();
            return ma;
        }

        public void themX(string maxe, string hangxe, string tenxe, string bienso)
        {
            Xe x = new Xe();
            x.Ma_Xe = maxe;
            x.LoaiXe = hangxe;
            x.TenXe = tenxe;
            x.BienSo = bienso;

            db.Xes.InsertOnSubmit(x);
            db.SubmitChanges();
        }

        public void suaX(string maxe, string hangxe, string tenxe, string bienso)
        {
            Xe x = db.Xes.Where(v => v.Ma_Xe == maxe).FirstOrDefault();
            x.LoaiXe = hangxe;
            x.TenXe = tenxe;
            x.BienSo = bienso;

            db.SubmitChanges();
        }

        public void XoaX(string maxe)
        {
            Xe x = db.Xes.Where(v => v.Ma_Xe == maxe).FirstOrDefault();
            db.Xes.DeleteOnSubmit(x);
            db.SubmitChanges();
        }
    }

    public class FormGiaXe
    {
        QLThueXeDataContext db = new QLThueXeDataContext();

        public List<ThongTinGiaXe> LayDSGiaXe()
        {
            var bien = (from gx in db.GiaThues
                        select new ThongTinGiaXe
                        {
                            id_gia = gx.ID_GiaThue,
                            hangxe = gx.LoaiXe,
                            giathue = gx.GiaThue1.Value,
                            ngayapdung = gx.NgayApDung.Value
                            
                        }).ToList<ThongTinGiaXe>();
            return bien;
        }

        public void themGiaXe(string hangxe, int giathue, DateTime ngayapdung )
        {
            GiaThue gt = new GiaThue();
            gt.LoaiXe = hangxe;
            gt.GiaThue1 = giathue;
            gt.NgayApDung = ngayapdung;

            db.GiaThues.InsertOnSubmit(gt);
            db.SubmitChanges();
        }

        public void suaGiaxe(int idgia, string hangxe, int giathue, DateTime ngayapdung)
        {
            GiaThue gt = db.GiaThues.Where(v => v.ID_GiaThue == idgia).FirstOrDefault();
            gt.LoaiXe = hangxe;
            gt.GiaThue1 = giathue;
            gt.NgayApDung = ngayapdung;

            db.SubmitChanges();
        }

        public void XoaGiaxXe(int idgia)
        {
            GiaThue gt = db.GiaThues.Where(v => v.ID_GiaThue == idgia).FirstOrDefault();
            db.GiaThues.DeleteOnSubmit(gt);
            db.SubmitChanges();
        }

    }

    public class FormThongKe
    {
         QLThueXeDataContext db = new QLThueXeDataContext();
        public List<ThongKe> ThongKeTongHop(DateTime tungay, DateTime denngay)
         {
             var bien = (from x in db.Xes
                         from hx in db.LoaiXes
                         from gt in db.GiaThues
                         from tx in db.ThueXes
                         where hx.LoaiXe1 == x.LoaiXe && hx.LoaiXe1 == gt.LoaiXe && x.Ma_Xe == tx.Ma_Xe && (tx.NgayGio_Thue.Value.Date >= tungay.Date && tx.NgayGio_Thue.Value.Date <= denngay.Date) && tx.Huy == false
                         && gt.NgayApDung == (from t in db.GiaThues where t.LoaiXe == hx.LoaiXe1 && t.NgayApDung.Value.Date <= tx.NgayGio_Thue.Value.Date orderby t.NgayApDung descending select t.NgayApDung).FirstOrDefault()
                         select new ThongKe
                         {
                             MaThueXe = tx.Ma_ThueXe,
                             MaXe = x.Ma_Xe,
                             HangXe = hx.LoaiXe1,
                             TenXe = x.TenXe,
                             BienSo = x.BienSo,
                             NgayThue = tx.NgayGio_Thue.Value,
                             GioThue = tx.SoGio_Thue.Value,
                             PhutThue = tx.SoPhut_Thue.Value,
                             GiaThue = gt.GiaThue1.Value,
                             TongGiaThue = tx.SoGio_Thue.Value * gt.GiaThue1.Value + tx.SoPhut_Thue.Value * gt.GiaThue1.Value / 60
                         }).ToList<ThongKe>();
             return bien;
         }

         public List<ThongKe2> tinhthongke(DateTime tungay, DateTime denngay)
        {
            List<ThongKe> tke = ThongKeTongHop(tungay, denngay);
            var bien = (from bang1 in
                            (from tt in tke
                             group tt by tt.MaXe into l //select l).ToList();
                             select new
                             {
                                 MaXe = l.Key,
                                 //HangXe = l.Select(d => d.HangXe),
                                 //TenXe = l.Select(d => d.TenXe),
                                 //BienSo = l.Select(d => d.BienSo),
                                 TuNgay = tungay,
                                 DenNgay = denngay,
                                 Gio = l.Sum(d => d.GioThue) + l.Sum(d => d.PhutThue) / 60,
                                 Phut = l.Sum(d => d.PhutThue) % 60 ,
                                 TongThoiGian = "" + (l.Sum(d => d.GioThue) + l.Sum(d => d.PhutThue) / 60) + " Giờ " + l.Sum(d => d.PhutThue) % 60 + " Phút",
                                 TongTien = l.Sum(d => d.TongGiaThue)
                             })
                        join bang2 in db.Xes on bang1.MaXe equals bang2.Ma_Xe
                        select new ThongKe2
                        {
                            MaXe = bang1.MaXe,
                            HangXe = bang2.LoaiXe,
                            TenXe = bang2.TenXe,
                            BienSo = bang2.BienSo,
                            TuNgay = bang1.TuNgay,
                            DenNgay = bang1.DenNgay,
                            ThoiGian = bang1.TongThoiGian,
                            TongTien = bang1.TongTien
                        }).ToList<ThongKe2>();
            return bien;
            //dataGridViewX2.DataSource = bien;
        }

         public List<ThongKeHuyThue> ThongKeHuy(DateTime tungay, DateTime denngay)
         {
             var bien = (from nv in db.NHANVIENs
                         from kh in db.KhachHangs
                         from tx in db.ThueXes
                         where nv.MA_NV == tx.NhanVien_Lap && nv.MA_NV == tx.NhanVien_Huy && tx.ID_KhachHang == kh.ID_KhachHang && tx.Huy == true && (tx.NgayGio_Thue.Value.Date >= tungay.Date && tx.NgayGio_Thue.Value.Date <= denngay.Date)
                         select new ThongKeHuyThue
                         {
                             MaXe = tx.Ma_Xe,
                             KhachHang = kh.HoTen_KH,
                             NgayThue = tx.NgayGio_Thue.Value.Date,
                             GioThue = tx.NgayGio_Thue.Value.Hour + ":" + tx.NgayGio_Thue.Value.Minute,
                             SoGio = tx.SoGio_Thue.Value,
                             SoPhut = tx.SoPhut_Thue.Value,
                             NVThue = nv.HOTEN_NV,
                             NVHuy = nv.HOTEN_NV,
                             LyDo = tx.LyDoHuy
                         }).ToList<ThongKeHuyThue>();
             return bien;
         }

        public List<ThongKeChuaThanhToan> ThongKeChuaThanhToan(DateTime tungay, DateTime denngay)
         {
             var bien = (from dg in db.GiaThues
                         from kh in db.KhachHangs
                         from tx in db.ThueXes
                         from hx in db.LoaiXes
                         from x in db.Xes
                         where tx.Ma_Xe == x.Ma_Xe && x.LoaiXe == hx.LoaiXe1 && hx.LoaiXe1 == dg.LoaiXe && tx.ID_KhachHang == kh.ID_KhachHang && (tx.NgayGio_Thue.Value.Date >= tungay.Date && tx.NgayGio_Thue.Value.Date <= denngay.Date)
                         && dg.NgayApDung == (from t in db.GiaThues where t.LoaiXe == hx.LoaiXe1 && t.NgayApDung.Value.Date <= tx.NgayGio_Thue.Value.Date orderby t.NgayApDung descending select t.NgayApDung).FirstOrDefault()
                         && tx.NoTien == false && tx.Huy == false
                         select new ThongKeChuaThanhToan
                         {
                             mathuexe = tx.Ma_ThueXe,
                             maxe = tx.Ma_Xe,
                             khachhang = kh.HoTen_KH,
                             sdt = kh.SDT_KH,
                             ngaythue = tx.NgayGio_Thue.Value,
                             giobd = tx.NgayGio_Thue.Value.Hour +":"+ tx.NgayGio_Thue.Value.Minute,
                             thoigianthue = tx.SoGio_Thue +"Giờ "+ tx.SoPhut_Thue +"Phút",
                             dongia = dg.GiaThue1.Value,
                             thanhtien = tx.SoGio_Thue.Value * dg.GiaThue1.Value + tx.SoPhut_Thue.Value * dg.GiaThue1.Value / 60,
                         }).ToList<ThongKeChuaThanhToan>();
             return bien;
         }

        public List<ThongKeChuaThanhToan> ThongKeChuaThanhToan2(string abc)
        {
            var bien = (from dg in db.GiaThues
                        from kh in db.KhachHangs
                        from tx in db.ThueXes
                        from hx in db.LoaiXes
                        from x in db.Xes
                        where tx.Ma_Xe == x.Ma_Xe && x.LoaiXe == hx.LoaiXe1 && hx.LoaiXe1 == dg.LoaiXe && tx.ID_KhachHang == kh.ID_KhachHang && (kh.HoTen_KH.Contains(abc) || kh.SDT_KH.Contains(abc))
                        && dg.NgayApDung == (from t in db.GiaThues where t.LoaiXe == hx.LoaiXe1 && t.NgayApDung.Value.Date <= tx.NgayGio_Thue.Value.Date orderby t.NgayApDung descending select t.NgayApDung).FirstOrDefault()
                        && tx.NoTien == false && tx.Huy == false
                        select new ThongKeChuaThanhToan
                        {
                            mathuexe = tx.Ma_ThueXe,
                            maxe = tx.Ma_Xe,
                            khachhang = kh.HoTen_KH,
                            sdt = kh.SDT_KH,
                            ngaythue = tx.NgayGio_Thue.Value,
                            giobd = tx.NgayGio_Thue.Value.Hour + ":" + tx.NgayGio_Thue.Value.Minute,
                            thoigianthue = tx.SoGio_Thue + "Giờ " + tx.SoPhut_Thue + "Phút",
                            dongia = dg.GiaThue1.Value,
                            thanhtien = tx.SoGio_Thue.Value * dg.GiaThue1.Value + tx.SoPhut_Thue.Value * dg.GiaThue1.Value / 60,
                        }).ToList<ThongKeChuaThanhToan>();
            return bien;
        }

        public List<ThongKeChuaThanhToan> ThongKeDaThanhToan(DateTime tungay, DateTime denngay)
        {
            var bien = (from dg in db.GiaThues
                        from kh in db.KhachHangs
                        from tx in db.ThueXes
                        from hx in db.LoaiXes
                        from x in db.Xes
                        where tx.Ma_Xe == x.Ma_Xe && x.LoaiXe == hx.LoaiXe1 && hx.LoaiXe1 == dg.LoaiXe && tx.ID_KhachHang == kh.ID_KhachHang && (tx.NgayGio_Thue.Value.Date >= tungay.Date && tx.NgayGio_Thue.Value.Date <= denngay.Date)
                        && dg.NgayApDung == (from t in db.GiaThues where t.LoaiXe == hx.LoaiXe1 && t.NgayApDung.Value.Date <= tx.NgayGio_Thue.Value.Date orderby t.NgayApDung descending select t.NgayApDung).FirstOrDefault()
                        && tx.NoTien == true && tx.Huy == false
                        select new ThongKeChuaThanhToan
                        {
                            mathuexe = tx.Ma_ThueXe,
                            maxe = tx.Ma_Xe,
                            khachhang = kh.HoTen_KH,
                            sdt = kh.SDT_KH,
                            ngaythue = tx.NgayGio_Thue.Value,
                            giobd = tx.NgayGio_Thue.Value.Hour + ":" + tx.NgayGio_Thue.Value.Minute,
                            thoigianthue = tx.SoGio_Thue + "Giờ " + tx.SoPhut_Thue + "Phút",
                            dongia = dg.GiaThue1.Value,
                            thanhtien = tx.SoGio_Thue.Value * dg.GiaThue1.Value + tx.SoPhut_Thue.Value * dg.GiaThue1.Value / 60,
                        }).ToList<ThongKeChuaThanhToan>();
            return bien;
        }
    }

    public class FormThongKeChiTiet
    {
        QLThueXeDataContext db = new QLThueXeDataContext();
        public List<ThongKe> ThongKeTongHop(string maxe, DateTime tungay, DateTime denngay)
        {
            var bien = (from x in db.Xes
                        from hx in db.LoaiXes
                        from gt in db.GiaThues
                        from tx in db.ThueXes
                        where x.Ma_Xe == maxe && hx.LoaiXe1 == x.LoaiXe && hx.LoaiXe1 == gt.LoaiXe && x.Ma_Xe == tx.Ma_Xe && (tx.NgayGio_Thue.Value.Date >= tungay.Date && tx.NgayGio_Thue.Value.Date <= denngay.Date) && tx.Huy == false
                        && gt.NgayApDung == (from t in db.GiaThues where t.LoaiXe == hx.LoaiXe1 && t.NgayApDung.Value.Date <= tx.NgayGio_Thue.Value.Date orderby t.NgayApDung descending select t.NgayApDung).FirstOrDefault()
                        select new ThongKe
                        {
                            MaThueXe = tx.Ma_ThueXe,
                            MaXe = x.Ma_Xe,
                            HangXe = hx.LoaiXe1,
                            TenXe = x.TenXe,
                            BienSo = x.BienSo,
                            NgayThue = tx.NgayGio_Thue.Value,
                            GioThue = tx.SoGio_Thue.Value,
                            PhutThue = tx.SoPhut_Thue.Value,
                            GiaThue = gt.GiaThue1.Value,
                            TongGiaThue = tx.SoGio_Thue.Value * gt.GiaThue1.Value + tx.SoPhut_Thue.Value * gt.GiaThue1.Value / 60,
                            thanhtoan = tx.NoTien.Value
                        }).ToList<ThongKe>();
            return bien;
        }
    }

    public class FormThongTinNV
    {
        QLThueXeDataContext db = new QLThueXeDataContext();

    }
}
